using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Metro.Admin.BusinessEntities;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Transactions;

namespace Metro.Admin.DataAccess
{
    public class Order
    {
        Database metroDb = DatabaseFactory.CreateDatabase("metroDB");

        public bool ExistsOrder(Metro.Admin.BusinessEntities.Order order)
        { 
          Guid idOrder =  new Guid(order.Id);
          bool retFlag = false;
          object retValue = null;

          try
          {
              string sqlCommand = "SELECT [id] FROM [Order] WHERE [id]=@id";

              DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

              metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idOrder);

              retValue = metroDb.ExecuteScalar(dbCommand);

              if (retValue != null)
              {
                  retFlag = true;
              }
          }
          catch (Exception ex)
          {
              throw ex;
          }

          return retFlag; 
        }

        public void AddOrder(Metro.Admin.BusinessEntities.Order order, bool withdraw)
        {
            Guid idUser = new Guid(order.UserId);
            Guid idOrder =  new Guid(order.Id);
            Guid idCustomer = new Guid(order.CustomerId); 
            Customer customer = null;
            AdminDataSource customerDA = new AdminDataSource();
            decimal futureTransitSellCredit = 0;
            decimal futureTransitBuyCredit = 0;

            try
            {
                customer = customerDA.GetCustomer(order.CustomerId);
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                {
                    string sqlCommand = "INSERT INTO [Order] ([id] ,[idUser],[date],[state]," + "[type],[businessType] ,[class],[amount],[price]," +
                                        "[market] ,[instruction],[priceOPCF],[expiration],[swap]) " +
                                        "VALUES (@id,@idUser,@date,@state,@type,@businessType,@class,@amount," + "@price,@market,@instruction,@priceOPCF,@expiration,@swap)";

                    DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                    metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idOrder);
                    metroDb.AddInParameter(dbCommand, "@idUser", DbType.Guid, idUser);
                    metroDb.AddInParameter(dbCommand, "@date", DbType.DateTime, DateTime.Now);
                    metroDb.AddInParameter(dbCommand, "@state", DbType.String, OrderServices.OrderState2Str(order.State));
                    metroDb.AddInParameter(dbCommand, "@type", DbType.String, OrderServices.OrderType2Str(order.Type));
                    metroDb.AddInParameter(dbCommand, "@businessType", DbType.String, OrderServices.BusinessType2Str(order.OrderBusinessType));
                    metroDb.AddInParameter(dbCommand, "@class", DbType.String, OrderServices.OrderClass2Str(order.OrderClass));
                    metroDb.AddInParameter(dbCommand, "@amount", DbType.Currency, order.Amount);
                    metroDb.AddInParameter(dbCommand, "@price", DbType.Currency, order.OriginalPrice);
                    metroDb.AddInParameter(dbCommand, "@market", DbType.String, OrderServices.Market2Str(order.Market));
                    metroDb.AddInParameter(dbCommand, "@instruction", DbType.String, OrderServices.Instruction2Str(order.OrderInstruction));
                    metroDb.AddInParameter(dbCommand, "@priceOPCF", DbType.Currency, order.OpcfPrice);
                    metroDb.AddInParameter(dbCommand, "@expiration", DbType.Int32, order.ExpirationNumber);
                    metroDb.AddInParameter(dbCommand, "@swap", DbType.Currency, order.Swap);

                    metroDb.ExecuteNonQuery(dbCommand);

                    if (withdraw)
                    {
                       
                        futureTransitBuyCredit = customer.TransitBuyCredit + order.Amount;
                        futureTransitSellCredit = customer.TransitSellCredit + order.Amount;

                        if (customer.CustomerType.Equals(CustomerType.OPCF))
                        {
                            sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit, transitSellCredit = @transitSellCredit WHERE (idCustomer = @idCustomer)";
                            dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                            metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                            metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                            metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                            metroDb.ExecuteNonQuery(dbCommand);
                        }

                        if (customer.CustomerType.Equals(CustomerType.Cambiario))
                        {
                            if (order.OrderBusinessType.Equals(BusinessType.Buy))
                            {
                                sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit WHERE (idCustomer = @idCustomer)";
                                dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                                metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                                metroDb.ExecuteNonQuery(dbCommand);
                            }
                            else if (order.OrderBusinessType.Equals(BusinessType.Sell))
                            {
                                sqlCommand = "UPDATE Customer SET transitSellCredit = @transitSellCredit WHERE (idCustomer = @idCustomer)";
                                dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                                metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                                metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                                metroDb.ExecuteNonQuery(dbCommand);
                            }
                        }
                    }

                    scope.Complete();  
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateOrder(Metro.Admin.BusinessEntities.Order order)
        {
            Guid idOrder = new Guid(order.Id);
            Guid idCustomer = new Guid(order.CustomerId);
            Customer customer = null;
            AdminDataSource customerDA = new AdminDataSource();
            decimal futureTransitSellCredit = 0;
            decimal futureTransitBuyCredit = 0;

            try
            {
                customer = customerDA.GetCustomer(order.CustomerId);
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                {
                    
                    string sqlCommand = "UPDATE [Order] SET [amount] = @amount,[price] = @price," +
                                        "[priceOPCF] = @priceOPCF,[expiration] = @expiration,[swap] = @swap " +
                                        "WHERE [id] = @id";

                    DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                    metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idOrder);

                    metroDb.AddInParameter(dbCommand, "@amount", DbType.Currency, order.Amount);
                    metroDb.AddInParameter(dbCommand, "@price", DbType.Currency, order.OriginalPrice);
                    metroDb.AddInParameter(dbCommand, "@priceOPCF", DbType.Currency, order.OpcfPrice);
                    metroDb.AddInParameter(dbCommand, "@expiration", DbType.Int32, order.ExpirationNumber);
                    metroDb.AddInParameter(dbCommand, "@swap", DbType.Currency, order.Swap);

                    metroDb.ExecuteNonQuery(dbCommand);

                    
                    futureTransitBuyCredit = customer.TransitBuyCredit - order.OldAmount + order.Amount;
                    futureTransitSellCredit = customer.TransitSellCredit - order.OldAmount + order.Amount;

                    if (customer.CustomerType.Equals(CustomerType.OPCF))
                    {
                        sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit, transitSellCredit = @transitSellCredit WHERE (idCustomer = @idCustomer)";
                        dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                        metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                        metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                        metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                        metroDb.ExecuteNonQuery(dbCommand);
                    }

                    if (customer.CustomerType.Equals(CustomerType.Cambiario))
                    {
                        if (order.OrderBusinessType.Equals(BusinessType.Buy))
                        {
                            sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit WHERE (idCustomer = @idCustomer)";
                            dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                            metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                            metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                            metroDb.ExecuteNonQuery(dbCommand);
                        }
                        else if (order.OrderBusinessType.Equals(BusinessType.Sell))
                        {
                            sqlCommand = "UPDATE Customer SET transitSellCredit = @transitSellCredit WHERE (idCustomer = @idCustomer)";
                            dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                            metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                            metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                            metroDb.ExecuteNonQuery(dbCommand);
                        }
                    }

                    scope.Complete();  
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteOrder(Metro.Admin.BusinessEntities.Order order)
        {
            Guid idOrder = new Guid(order.Id);
            Guid idCustomer = new Guid(order.CustomerId);
            Customer customer = null;
            AdminDataSource customerDA = new AdminDataSource();
            decimal futureTransitSellCredit = 0;
            decimal futureTransitBuyCredit = 0;

            try
            {
                customer = customerDA.GetCustomer(order.CustomerId);

                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                {
                    string sqlCommand = "UPDATE [Order] SET [state] = @state WHERE [id] = @id";

                    DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                    metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idOrder);

                    metroDb.AddInParameter(dbCommand, "@state", DbType.String, OrderServices.OrderState2Str(OrderState.Cancel));
                    metroDb.ExecuteNonQuery(dbCommand);

                    
                    futureTransitBuyCredit = customer.TransitBuyCredit - order.Amount;
                    futureTransitSellCredit = customer.TransitSellCredit - order.Amount;

                    if (customer.CustomerType.Equals(CustomerType.OPCF))
                    {
                        sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit, transitSellCredit = @transitSellCredit WHERE (idCustomer = @idCustomer)";
                        dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                        metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                        metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                        metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                        metroDb.ExecuteNonQuery(dbCommand);
                    }

                    if (customer.CustomerType.Equals(CustomerType.Cambiario))
                    {
                        if (order.OrderBusinessType.Equals(BusinessType.Buy))
                        {
                            sqlCommand = "UPDATE Customer SET transitBuyCredit = @transitBuyCredit WHERE (idCustomer = @idCustomer)";
                            dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                            metroDb.AddInParameter(dbCommand, "@transitBuyCredit", DbType.Currency, futureTransitBuyCredit);
                            metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                            metroDb.ExecuteNonQuery(dbCommand);
                        }
                        else if (order.OrderBusinessType.Equals(BusinessType.Sell))
                        {
                            sqlCommand = "UPDATE Customer SET transitSellCredit = @transitSellCredit WHERE (idCustomer = @idCustomer)";
                            dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
                            metroDb.AddInParameter(dbCommand, "@transitSellCredit", DbType.Currency, futureTransitSellCredit);
                            metroDb.AddInParameter(dbCommand, "@idCustomer", DbType.Guid, idCustomer);
                            metroDb.ExecuteNonQuery(dbCommand);
                        }
                    }

                    scope.Complete(); 
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<Metro.Admin.BusinessEntities.Order> GetOrdersFromUser(string idUser, DateTime date, string market)
        {
            List<Metro.Admin.BusinessEntities.Order> orders = new List<Metro.Admin.BusinessEntities.Order>();
            Metro.Admin.BusinessEntities.Order order = null;
            Metro.Admin.BusinessEntities.User user = null;
            Metro.Admin.BusinessEntities.Customer customer = null;

            Admin.DataAccess.AdminDataSource adminDS = new AdminDataSource();
            string sqlCommand = "";

            try
            {
                 sqlCommand = "SELECT [Order].id, [Order].idUser, [Order].date, [Order].state, [Order].type, [Order].businessType," + 
                                     "[Order].class, [Order].amount, [Order].price, [Order].market," +  
                                     "[Order].instruction, [Order].priceOPCF, [Order].expiration, [Order].swap " + 
                                     "FROM [Order] LEFT OUTER JOIN " +
                                     "[Transaction] ON [Order].id = [Transaction].idOrderPost " +
                                     "WHERE [Transaction].idOrderPost IS NULL AND [Order].date >= @date AND [Order].market = @market AND [Order].state='A' ";

                 if (!idUser.Equals(String.Empty))
                     sqlCommand += "AND [Order].idUser = @idUser";  

                 DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);

                 if (!idUser.Equals(String.Empty))
                 {
                     Guid idUserForQuery = new Guid(idUser);
                     metroDb.AddInParameter(dbCommand, "@idUser", DbType.Guid, idUserForQuery);
                 }

                 metroDb.AddInParameter(dbCommand, "@date", DbType.DateTime, date);
                 metroDb.AddInParameter(dbCommand, "@market", DbType.String, market);


                 using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
                 {
                     while (dataReader.Read())
                     {
                         order = new Metro.Admin.BusinessEntities.Order();
                         order.UserId = dataReader["idUser"].ToString(); 
                         order.Id = dataReader["id"].ToString();
                         
                         user = adminDS.GetUser(order.UserId);  
                         order.CustomerId = user.CustomerId; 
                         
                         customer =  adminDS.GetCustomer(order.CustomerId);
   
                         order.CustomerName = customer.Name; 
                         order.Commission  = customer.Commission;
                         order.TechnicalName = customer.TechnicalName;  
                         order.Amount = Convert.ToDecimal(dataReader["amount"].ToString()); 
                         order.OriginalPrice = Convert.ToDecimal(dataReader["price"].ToString()); 
                         order.DirtyPrice = order.OriginalPrice + customer.Commission; 
                         order.OrderBusinessType = OrderServices.Letter2BusinessType(dataReader["businessType"].ToString());
                         order.Type = OrderServices.Letter2OrderType(dataReader["type"].ToString());
                         order.OrderClass = OrderServices.Letter2OrderClass(dataReader["class"].ToString());    
                           
                         order.UserName = user.Name;
                         order.Market = OrderServices.Letter2Market(dataReader["market"].ToString()); 
                         order.State = OrderServices.Letter2OrderState(dataReader["state"].ToString()); 
                         order.Date = Convert.ToDateTime(dataReader["date"].ToString());  

                         orders.Add(order); 
                     }
                 }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "sqlCommand -" + sqlCommand);
            }

            return orders;  
        }

        public Metro.Admin.BusinessEntities.Order GetOrderById(string id)
        {
            Metro.Admin.BusinessEntities.Order order = null;
            Guid idOrder = new Guid(id);
            Metro.Admin.BusinessEntities.User user = null;
            Metro.Admin.BusinessEntities.Customer customer = null;
            Admin.DataAccess.AdminDataSource adminDS = new AdminDataSource();  

          try
          {
              string sqlCommand = "SELECT id, idUser, date, [state], [type], businessType, class, amount, price, market, instruction," +                                       "priceOPCF, expiration, swap FROM [Order] WHERE (id = @id)";

              DbCommand dbCommand = metroDb.GetSqlStringCommand(sqlCommand);
              metroDb.AddInParameter(dbCommand, "@id", DbType.Guid, idOrder);

              using (IDataReader dataReader = metroDb.ExecuteReader(dbCommand))
              {
                  while (dataReader.Read())
                  {
                      order = new Metro.Admin.BusinessEntities.Order();

                      order.Id = dataReader["id"].ToString();
                      order.UserId = dataReader["idUser"].ToString();
                      user = adminDS.GetUser(order.UserId);
                      order.CustomerId = user.CustomerId;
                      customer = adminDS.GetCustomer(order.CustomerId);
                      order.CustomerName = customer.Name;
                      order.Commission = customer.Commission;
                      order.TechnicalName = customer.TechnicalName;
                      order.Amount = Convert.ToDecimal(dataReader["amount"].ToString());
                      order.OriginalPrice = Convert.ToDecimal(dataReader["price"].ToString());
                      order.DirtyPrice = order.OriginalPrice + customer.Commission;
                      order.OrderBusinessType = OrderServices.Letter2BusinessType(dataReader["businessType"].ToString());
                      order.Type = OrderServices.Letter2OrderType(dataReader["type"].ToString());
                      order.OrderClass = OrderServices.Letter2OrderClass(dataReader["class"].ToString());
                      order.UserId = dataReader["idUser"].ToString();
                      order.UserName = user.Name;
                      order.Market = OrderServices.Letter2Market(dataReader["market"].ToString());
                      order.State = OrderServices.Letter2OrderState(dataReader["state"].ToString());
                      order.Date = Convert.ToDateTime(dataReader["date"].ToString());  
                  }
              }
          }
          catch (Exception ex)
          {
              throw ex;
          }

          return order;
        
        }

       
    }
}